package edu.unc.ils.mrc.vocabulary;

import java.io.File;



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;

import javax.xml.namespace.QName;

import maui.stemmers.PorterStemmer;
import maui.stemmers.Stemmer;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.openrdf.concepts.skos.core.Concept;
import org.openrdf.elmo.ElmoModule;
import org.openrdf.elmo.sesame.SesameManager;
import org.openrdf.elmo.sesame.SesameManagerFactory;
import org.openrdf.repository.Repository;
import org.openrdf.repository.sail.SailRepository;
import org.openrdf.sail.nativerdf.NativeStore;

import edu.unc.ils.mrc.vocabulary.Term.MatchType;

/**
 * Implementation of the Vocabulary interface over an H2 embedded database.
 * @author cwillis
 *
 */
public class VocabularyH2 extends Vocabulary
{
    String vocabularyName;
    String vocabularyPath;
    Connection connection;
    
    Random random = new Random(System.nanoTime());
    
    Map<Integer, Map<Integer, String>> relationMap = new HashMap<Integer, Map<Integer, String>>();
    
    public static void main(String[] args) 
    {
        try
        {
            VocabularyH2 v = new VocabularyH2();
            Stemmer stemmer = new PorterStemmer();
            v.setStemmer(stemmer);
            v.setStopwords("src/main/resources/stopwords_en.txt");
            v.open("/Users/cwillis/dev/mrc/nlp/memai-mrc/src/main/resources/nalt");
            //v.createSchema();
            v.importFromSesame("/Users/cwillis/dev/hive/hive-data/nalt/naltStore");
            
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public int getNextRandom(int conceptId) throws Exception
    {
        return getNextRandom(conceptId, 1, 1, 1);
    }
    
    public int getNextRandom(int conceptId, int btWeight, int ntWeight, int rtWeight)
        throws Exception
    {
        double total = 0.0;
        
        List<Relation> relations = getRelatedConcepts(conceptId);
        
        // cumulative distribution
        double[] weights = new double[relations.size()];
        int[] concepts = new int[relations.size()];
        int i=0;
        for(Relation relation: relations)
        {
            String relationType = relation.getRelationType();
            concepts[i] = relation.getRelatedConceptId();
            if (relationType.equals("broader"))
                weights[i] = btWeight;
            else if (relationType.equals("narrower"))
                weights[i] = ntWeight;
            else
                weights[i] = rtWeight;
                
            total += weights[i];
            i++;
        }
       
       
        // Weighted random selection
        double choice = random.nextDouble() * total;
        
        int selected = 0;
        for (int j=0; j < i; j++) {
            double weight = weights[j];
            choice -= weight;
            if (choice < 0) {
                selected = j;
                break;
            }
        }
        if (concepts.length == 0) {
            double d = random.nextDouble();
            return (int)(relationMap.size() * d);
        }
        return concepts[selected];
    }
    
    public List<Term> lookupNonStemmed(String str) throws Exception
    {
        Connection con = getConnection();
        
        List<Term> matches = new ArrayList<Term>();
        if  (isStopword(str))
            return matches;
        
        PreparedStatement ps = con.prepareStatement("select pref_label, uri from concept where pref_label_lower = ?");
        ps.setString(1, str.toLowerCase());
        ResultSet rs = ps.executeQuery();
        while (rs.next())
        {
            Term term = new Term();
            term.setPrefLabel(rs.getString(1));
            term.setUri(rs.getString(2));
            term.setMatchType(MatchType.PREFERRED_LABEL);
            matches.add(term);
        }
        rs.close();
        ps.close();
        
        PreparedStatement ps2 = con.prepareStatement("select c.pref_label, uri from concept c, alternate_label al " + 
            " where al.concept_id = c.concept_id and al.alt_label_lower =?");
        ps2.setString(1, str.toLowerCase());
        ResultSet rs2 = ps2.executeQuery();
        while (rs2.next())
        {
            Term term = new Term();
            term.setPrefLabel(rs2.getString(1));
            term.setUri(rs2.getString(2));
            term.setMatchType(MatchType.ALTERNATE_LABEL);
            matches.add(term);
        }
        rs2.close();
        ps2.close();
        return matches;
            
    }
    
    
    public List<Term> lookup(String str) throws Exception 
    {
        Connection con = getConnection();
        
        List<Term> matches = new ArrayList<Term>();
        if  (isStopword(str))
            return matches;
        PreparedStatement ps = con.prepareStatement("select concept_id, pref_label, uri from concept where pref_label_stemmed = ?");
        ps.setString(1, str.toLowerCase());
        ResultSet rs = ps.executeQuery();
        while (rs.next())
        {
            Term term = new Term();
            term.setConceptId(rs.getInt(1));
            term.setPrefLabel(rs.getString(2));
            term.setUri(rs.getString(3));
            term.setMatchType(MatchType.PREFERRED_LABEL);
            matches.add(term);
        }
        rs.close();
        ps.close();
        
        PreparedStatement ps2 = con.prepareStatement("select c.pref_label, uri from concept c, alternate_label al " + 
            " where al.concept_id = c.concept_id and al.alt_label_stemmed =?");
        ps2.setString(1, str.toLowerCase());
        ResultSet rs2 = ps2.executeQuery();
        while (rs2.next())
        {
            Term term = new Term();
            term.setPrefLabel(rs2.getString(1));
            term.setUri(rs2.getString(2));
            term.setMatchType(MatchType.ALTERNATE_LABEL);
            matches.add(term);
        }
        rs2.close();
        ps2.close();
        return matches;
    }
    
    public List<Term> lookupWord(String str) throws Exception 
    {
        Connection con = getConnection();
        
        List<Term> matches = new ArrayList<Term>();
        if (isStopword(str.toLowerCase()))
            return matches;
        
        PreparedStatement ps = con.prepareStatement("select c.concept_id, c.pref_label, c.uri from concept c, word_concept_map wcm, word w where wcm.concept_id = c.concept_id and wcm.word_id = w.word_id and w.word_lower = ?");
        ps.setString(1, str.toLowerCase());
        ResultSet rs = ps.executeQuery();
        while (rs.next())
        {
            Term term = new Term();
            term.setConceptId(rs.getInt(1));
            term.setPrefLabel(rs.getString(2));
            term.setUri(rs.getString(3));
            term.setMatchType(MatchType.WORD);
            matches.add(term);
        }
        rs.close();
        ps.close();

        return matches;
    }

    public List<Term> lookupWordStemmed(String str) throws Exception 
    {
        Connection con = getConnection();
        
        List<Term> matches = new ArrayList<Term>();
        if (isStopword(str.toLowerCase()))
            return matches;
        
        PreparedStatement ps = con.prepareStatement("select c.concept_id, c.pref_label, c.uri from concept c, word_concept_map wcm, word w where wcm.concept_id = c.concept_id and wcm.word_id = w.word_id and w.word_stemmed = ?");
        ps.setString(1, str.toLowerCase());
        ResultSet rs = ps.executeQuery();
        while (rs.next())
        {
            Term term = new Term();
            term.setConceptId(rs.getInt(1));
            term.setPrefLabel(rs.getString(2));
            term.setUri(rs.getString(3));
            term.setMatchType(MatchType.WORD);
            matches.add(term);
        }
        rs.close();
        ps.close();

        return matches;
    }
    
    public void loadRelatedConcepts() throws Exception
    {
        //System.out.println("Loading concepts");
        Connection con = getConnection();

        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("select concept_id_1, concept_id_2, relation from concept_relation");
        while (rs.next())
        {
            int conceptId1 = rs.getInt(1);
            int conceptId2 = rs.getInt(2);
            String relation = rs.getString(3);
            
            Map<Integer, String> relations = relationMap.get(conceptId1);
            if (relations == null)
                relations = new HashMap<Integer, String>();
            
            relations.put(conceptId2, relation);
            relationMap.put(conceptId1, relations);
        }
        rs.close();
        s.close();
        //System.out.println("Loading concepts done");
        
    }
    
    public List<Relation> getRelatedConcepts(int conceptId) throws Exception
    {
        List<Relation> relations = new ArrayList<Relation>();
        
        if (relationMap.size() == 0)
            loadRelatedConcepts();
        
        Map<Integer, String> rels = relationMap.get(conceptId);
        if (rels != null)
        {
            for (Integer conceptId2: rels.keySet())
            {
                String relation = rels.get(conceptId2);
                
                Relation rel = new Relation();
                rel.setConceptId(conceptId);
                rel.setRelatedConceptId(conceptId2);
                rel.setRelationType(relation);
                relations.add(rel);
            }
        }
        /*
        List<Relation> relations = new ArrayList<Relation>();
        
        Connection con = getConnection();

        PreparedStatement ps = con.prepareStatement("select concept_id_2, relation from concept_relation where concept_id_1 = ?");
        ps.setInt(1, conceptId);
        ResultSet rs = ps.executeQuery();
        while (rs.next())
        {
            Relation rel = new Relation();
            rel.setConceptId(conceptId);
            rel.setRelatedConceptId(rs.getInt(1));
            rel.setRelationType(rs.getString(2));
            relations.add(rel);
        }
        rs.close();
        ps.close();
        */
        return relations;
    }
    public String getRelation(int conceptId1, int conceptId2) throws Exception
    {
        String relation = null;
        
        Connection con = getConnection();
       
        
        PreparedStatement ps = con.prepareStatement("select relation from concept_relation where concept_id_1 = ? and concept_id_2 = ?");
        ps.setInt(1, conceptId1);
        ps.setInt(2, conceptId2);
        ResultSet rs = ps.executeQuery();
        if (rs.next())
            relation = rs.getString(1);
        rs.close();
        ps.close();
        
        return relation;
    }
    
    public boolean isBroaderNarrower(String term1, String term2) throws Exception
    {
        Connection con = getConnection();
        
        int conceptId1 = getConceptId(con, term1);
        int conceptId2 = getConceptId(con, term2);
        
        String relation = getRelation(conceptId1, conceptId2);
        
        boolean isBN = false;
        if (relation != null && (relation.equals("broader") || relation.equals("narrower")))
            isBN =  true;
        else
            isBN = false;
        
        return isBN;
    }
    
    public boolean isRelated(String term1, String term2) throws Exception
    {
        
        Connection con = getConnection();
        
        int conceptId1 = getConceptId(con, term1);
        int conceptId2 = getConceptId(con, term2);
        
        String relation = getRelation(conceptId1, conceptId2);
        
        boolean isRelated = false;
        if (relation != null && relation.equals("related"))
            isRelated =  true;
        else
            isRelated = false;
        
        return isRelated;
    }    
    
    
    public void importFromSesame(String path) throws Exception
    {
        //this.vocabularyName = new File(path).getName();
        this.vocabularyPath = path;

        // Open the Sesame repository
        String indexes = "spoc,ospc";
        NativeStore store = new NativeStore(new File(vocabularyPath), indexes);
        Repository repository = new SailRepository(store);
        repository.initialize();
        ElmoModule module = new ElmoModule();
        SesameManagerFactory factory = new SesameManagerFactory(module, repository);
        SesameManager manager = factory.createElmoManager(); 
        
        Connection con = getConnection();
        for (Concept concept:  manager.findAll(Concept.class))
        {
            String prefLabel = concept.getSkosPrefLabel();
            Set<String> altLabels = concept.getSkosAltLabels();
            QName qname = concept.getQName();
            String uri = qname.getNamespaceURI() + qname.getLocalPart();
            insertConcept(con, uri, prefLabel, altLabels);
        }
        
        for (Concept concept:  manager.findAll(Concept.class))
        {
            int conceptId = getConceptId(con, concept.getSkosPrefLabel());
            
            Set<Concept> broaders = concept.getSkosBroaders();
            Iterator<Concept> bit = broaders.iterator();
            while (bit.hasNext())
            {
                try
                {
                    Concept broader = bit.next();
                    int broaderId = getConceptId(con, broader.getSkosPrefLabel());
                    addRelation(con, conceptId, broaderId, "broader");
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

           
            
            Set<Concept> narrowers = concept.getSkosNarrowers();
            Iterator<Concept> nit = narrowers.iterator();
            while (nit.hasNext())
            {
                try
                {
                    Concept narrower = nit.next();
                    int narrowerId = getConceptId(con, narrower.getSkosPrefLabel());
                    addRelation(con, conceptId, narrowerId, "narrower");
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            
            Set<Concept> relateds = concept.getSkosRelated();
            Iterator<Concept> rit = relateds.iterator();
            while (rit.hasNext())
            {
                try
                {
                    Concept related = rit.next();
                    int relatedId = getConceptId(con, related.getSkosPrefLabel());
                    addRelation(con, conceptId, relatedId, "related");
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        repository.shutDown();
        store.shutDown();
    }
    
    @Override
    public void open(String path) throws Exception 
    {
        this.vocabularyName = new File(path).getName();
        
        // Initialize an H2 connection pool
        String uri = "jdbc:h2:" + path;
        Class.forName("org.h2.Driver");

        ObjectPool connectionPool = new GenericObjectPool(null);
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(uri, "", "");
        new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);

        Class.forName("org.apache.commons.dbcp.PoolingDriver");
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        driver.registerPool(vocabularyName, connectionPool);
        connection =  DriverManager.getConnection("jdbc:apache:commons:dbcp:" + vocabularyName);
    }
    

    protected Connection getConnection() throws SQLException {
        return connection;
        //return DriverManager.getConnection("jdbc:apache:commons:dbcp:" + vocabularyName);
    }
    
    public String getPrefLabel(int conceptId) throws SQLException
    {
        Connection con = getConnection();
        String prefLabel = null;
        PreparedStatement ps = con.prepareStatement("select pref_label from concept where concept_id = ?");
        ps.setInt(1, conceptId);
        
        ResultSet rs = ps.executeQuery();
        if (rs.next())
            prefLabel = rs.getString(1);
        
        ps.close();
        
        return prefLabel;
    }
    
    public int getConceptId(String prefLabel) throws SQLException
    {
        Connection con = getConnection();
        return getConceptId(con, prefLabel);
    }
    
    protected int getConceptId(Connection con, String prefLabel) throws SQLException
    {
        int conceptId = -1;
        PreparedStatement ps = con.prepareStatement("select concept_id from concept where pref_label_lower = ?");
        ps.setString(1, prefLabel.toLowerCase());
        
        ResultSet rs = ps.executeQuery();
        if (rs.next())
            conceptId = rs.getInt(1);
        
        ps.close();
        rs.close();
        
        return conceptId;
    }
    
    protected int insertConcept(Connection con, String uri, String prefLabel, Set<String> altLabels)
        throws SQLException
    {
        PreparedStatement ps = con.prepareStatement("insert into concept " +
                "(concept_id, uri, pref_label, pref_label_lower, pref_label_stemmed) " +
                " values (?, ?, ?, ?, ?)");
        
        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("SELECT CONCEPT_SEQ.nextval AS id FROM dual;");
        int conceptId = -1;
        if (rs.next())
        {    
            conceptId = rs.getInt(1);

            ps.setInt(1, conceptId);
            ps.setString(2, uri);
            ps.setString(3, prefLabel);
            ps.setString(4, prefLabel.toLowerCase());
            ps.setString(5, pseudoPhrase(prefLabel));
            ps.executeUpdate();
        }
        rs.close();
        s.close();
        ps.close();
        
        addWords(con, conceptId, prefLabel);
        
        PreparedStatement ps2 = con.prepareStatement("insert into alternate_label " +
                "(concept_id, alt_label, alt_label_lower, alt_label_stemmed) " +
                " values (?, ?, ?, ?)");
        
        for (String altLabel: altLabels)
        {
            ps2.setInt(1, conceptId);
            ps2.setString(2, altLabel);
            ps2.setString(3, altLabel.toLowerCase());
            ps2.setString(4, pseudoPhrase(altLabel));
            ps2.executeUpdate();
            
            addWords(con, conceptId, altLabel);
        }
        ps2.close();
        return conceptId;
    }
    
    protected int getWordId(Connection con, String word) throws SQLException
    {
        int wordId = -1;
        PreparedStatement ps = con.prepareStatement("select word_id from word where word_lower = ?");
        ps.setString(1, word.toLowerCase());
        ResultSet rs = ps.executeQuery();
        if (rs.next())
            wordId = rs.getInt(1);
        
        rs.close();
        ps.close();
        return wordId;
            
    }
    
    protected int getWordCount(Connection con, int wordId, int conceptId) throws SQLException
    {
        int count = 0;
        PreparedStatement ps = con.prepareStatement("select count from word_concept_map where word_id = ? and concept_id = ?");
        ps.setInt(1, wordId);
        ps.setInt(2, conceptId);
        ResultSet rs = ps.executeQuery();
        if (rs.next())
            count = rs.getInt(1);
        
        rs.close();
        ps.close();
        return count;
            
    }
    
    protected void addWords(Connection con, int conceptId, String phrase) throws SQLException
    {
        String[] words = phrase.split(" ");
        
        PreparedStatement ps = con.prepareStatement("insert into word " +
                "(word_id, word, word_lower, word_stemmed) " +
                " values (?, ?, ?, ?)");
        
        PreparedStatement ps2 = con.prepareStatement("insert into word_concept_map " +
                "(word_id, concept_id, count) values (?, ?, ?)");
        
        PreparedStatement ps3 = con.prepareStatement("update word_concept_map set count=? where word_id = ? and concept_id = ?");
        
        for (String word: words) 
        {
            if (!isStopword(word))
            {
                int wordId = getWordId(con, word);
                if (wordId == -1)
                {
                    Statement s = con.createStatement();
                    ResultSet rs = s.executeQuery("SELECT WORD_SEQ.nextval AS id FROM dual;");
                    if (rs.next())
                        wordId = rs.getInt(1);
                    rs.close();
                    s.close();

                    ps.setInt(1, wordId);
                    ps.setString(2, word);
                    ps.setString(3, word.toLowerCase());
                    ps.setString(4, pseudoPhrase(word));
                    ps.executeUpdate();
                }
                
                int count = getWordCount(con, wordId, conceptId);
                if (count == 0)
                {
                    ps2.setInt(1, wordId);
                    ps2.setInt(2, conceptId);
                    ps2.setInt(3, 1);
                    ps2.executeUpdate();
                }
                else
                {
                    ps3.setInt(1, count);
                    ps3.setInt(2, wordId);
                    ps3.setInt(3, conceptId);
                    ps3.executeUpdate();
                }
            }
        }
        ps.close();
        ps2.close();
        ps3.close();
    }
    
    protected void addRelation(Connection con, int conceptId1, int conceptId2, String relationType)
        throws SQLException
    {
        PreparedStatement ps = con.prepareStatement("insert into concept_relation (concept_id_1, concept_id_2, relation) " + 
                " values (?,?,?)");
        ps.setInt(1, conceptId1);
        ps.setInt(2, conceptId2);
        ps.setString(3, relationType);
        ps.executeUpdate();
        ps.close();
     }
    
    public void createSchema() throws SQLException
    {
        Connection con = null;
        Statement s = null;
        try
        {
            con = getConnection();
            s = con.createStatement();
            
            s.execute("CREATE SEQUENCE concept_seq;");
            
            s.execute("CREATE SEQUENCE word_seq;");
            
            s.execute("CREATE TABLE concept ( " +
                      " concept_id number, " +
                      " uri varchar(256), " +
                      " pref_label varchar(256), " +
                      " pref_label_lower varchar(256), " +
                      " pref_label_stemmed varchar(256) );" 
                  );
            s.execute(" CREATE INDEX idx10 on concept(concept_id)");
            s.execute(" CREATE INDEX idx11 on concept(pref_label_lower)");
            s.execute(" CREATE INDEX idx12 on concept(pref_label_stemmed)");
            s.execute(" CREATE INDEX idx13 on concept(uri)");
            
            s.execute("CREATE TABLE alternate_label ( " +
                      " concept_id number, " +
                      " alt_label varchar(256), " +
                      " alt_label_lower varchar(256), " +
                      " alt_label_stemmed varchar(256) );" 
                  );
            s.execute(" CREATE INDEX idx21 on alternate_label(concept_id)");
            s.execute(" CREATE INDEX idx22 on alternate_label(alt_label_lower)");
            s.execute(" CREATE INDEX idx23 on alternate_label(alt_label_stemmed)");
            
            s.execute("CREATE TABLE word ( " + 
                      " word_id number, " + 
                      " word varchar(256), " + 
                      " word_lower varchar(256), " +
                      " word_stemmed varchar(256) );" 
                  );
            s.execute(" CREATE INDEX idx30 on word(word_id)");
            s.execute(" CREATE INDEX idx31 on word(word)");
            s.execute(" CREATE INDEX idx33 on word(word_lower)");
            s.execute(" CREATE INDEX idx34 on word(word_stemmed)");
            
            s.execute("CREATE TABLE word_concept_map ( " + 
                      " word_id number, " + 
                      " concept_id number, " + 
                      " count number );"
                  );
            s.execute(" CREATE INDEX idx40 on word_concept_map(word_id, concept_id)");
            
            s.execute("CREATE TABLE concept_relation ( " +
                      " concept_id_1 number, " + 
                      " concept_id_2 number, " +
                      " relation varchar(50) );"
                  );            
            s.execute(" CREATE INDEX idx50 on concept_relation(concept_id_1, concept_id_2, relation)");
            s.execute(" CREATE INDEX idx51 on concept_relation(concept_id_1)");
            s.execute(" CREATE INDEX idx52 on concept_relation(concept_id_2)");
            
        } finally {
            try {
                if (con != null)
                    con.close();
                if (s != null)
                    s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    @Override
    public void close() throws Exception {
        // TODO Auto-generated method stub
        
    }

    @Override
    public int getSize() throws Exception {
        int size = 0;
        
        Connection con = getConnection();
        
        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("select count(*) from concept");
        if (rs.next())
            size = rs.getInt(1);
        rs.close();
        s.close();
        
        return size;
    }
}
